﻿namespace Hidistro.SqlDal.Comments
{
    using Hidistro.Entities;
    using Hidistro.Entities.Comments;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;

    public class AfficheDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public bool AddAffiche(AfficheInfo affiche)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_Affiche(Title, Content, AddedDate) VALUES (@Title, @Content, @AddedDate)");
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, affiche.Title);
            this.database.AddInParameter(sqlStringCommand, "Content", DbType.String, affiche.Content);
            this.database.AddInParameter(sqlStringCommand, "AddedDate", DbType.DateTime, affiche.AddedDate);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }

        public bool DeleteAffiche(int afficheId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_Affiche WHERE AfficheId = @AfficheId");
            this.database.AddInParameter(sqlStringCommand, "AfficheId", DbType.Int32, afficheId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }

        public AfficheInfo GetAffiche(int afficheId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Affiche WHERE AfficheId = @AfficheId");
            this.database.AddInParameter(sqlStringCommand, "AfficheId", DbType.Int32, afficheId);
            AfficheInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateAffiche(reader);
                }
            }
            return info;
        }

        public List<AfficheInfo> GetAfficheList()
        {
            List<AfficheInfo> list = new List<AfficheInfo>();
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Affiche ORDER BY AddedDate DESC");
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    AfficheInfo item = DataMapper.PopulateAffiche(reader);
                    list.Add(item);
                }
            }
            return list;
        }

        public AfficheInfo GetFrontOrNextAffiche(int afficheId, string type)
        {
            string query = string.Empty;
            if (type == "Next")
            {
                query = "SELECT TOP 1 * FROM Hishop_Affiche WHERE AfficheId < @AfficheId  ORDER BY AfficheId DESC";
            }
            else
            {
                query = "SELECT TOP 1 * FROM Hishop_Affiche WHERE AfficheId > @AfficheId ORDER BY AfficheId ASC";
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            this.database.AddInParameter(sqlStringCommand, "AfficheId", DbType.Int32, afficheId);
            AfficheInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateAffiche(reader);
                }
            }
            return info;
        }

        public bool UpdateAffiche(AfficheInfo affiche)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Affiche SET Title = @Title, AddedDate = @AddedDate, Content = @Content WHERE AfficheId = @AfficheId");
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, affiche.Title);
            this.database.AddInParameter(sqlStringCommand, "Content", DbType.String, affiche.Content);
            this.database.AddInParameter(sqlStringCommand, "AddedDate", DbType.DateTime, affiche.AddedDate);
            this.database.AddInParameter(sqlStringCommand, "AfficheId", DbType.Int32, affiche.AfficheId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }
    }
}

